package codemaster.persistence;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;

import codemaster.core.Constant;
import codemaster.core.Helper;

import com.Ostermiller.util.ExcelCSVParser;
import common.util.FileUtility;

public class SqlBuilder
{
	/**
	 * Builds a sql script file
	 * 
	 * @param inputFile the input file
	 * @param outputDir the output directory
	 * @param dbType the database type
	 */
	public void build(File inputFile, File outputDir, String dbType)
	{
		// Determines the table name from the input file name
		String tableName = getTableName(FileUtility
				.getFileNameWithoutExtension(inputFile));

		BufferedWriter bfw = null;
		BufferedReader bfr = null;
		try
		{
			// Reads the table columns
			bfr = new BufferedReader(new FileReader(inputFile));
			String[][] table = ExcelCSVParser.parse(bfr);
			bfr.close();

			// Creates the sql script file at the destination folder
			String dbDir = Helper.getPackageDir(outputDir, dbType);
			(new File(dbDir)).mkdirs();
			bfw = new BufferedWriter(new FileWriter(dbDir + File.separator
					+ tableName + ".sql"));

			bfw.write("DROP TABLE IF EXISTS " + tableName + ";\n");
			bfw.write("CREATE TABLE " + tableName + " (\n");

			// Writes table columns
			for (int i = 1; i < table.length; i++)
			{
				// If non-persistent attribute is not Y, writes the column
				if (!table[i][9].trim().equalsIgnoreCase(Constant.YES))
				{
					bfw.write(getColumn(getColumnName(table[i][Constant.C_NAME]),
							table[i][Constant.C_DATA_TYPE], table[i][Constant.C_NULLABLE],
							table[i][Constant.C_DEFAULT], table[i][Constant.C_AUTO_INCREMENT]));
				}
			}
			/*bfw.write(getPrimaryKeyConstraint(tableName, table));
			String uniqueConstraint = getUniqueConstraint(tableName, table);
			if (uniqueConstraint != null)
			{
				bfw.write(",\n" + uniqueConstraint);
			}
			*/
			bfw.write("KEY (`id`),\n");
			bfw.write("KEY `created_at` (`created_at`)\n");
			bfw.write(") ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8\n");
//			bfw.write("`game_area` varchar(50) DEFAULT NULL,");
			bfw.write("/*!50100 PARTITION BY LIST (day(`created_at`))\n");
			bfw.write("(PARTITION p0 VALUES IN (1) ENGINE = MyISAM,\n");
			bfw.write(" PARTITION p1 VALUES IN (2) ENGINE = MyISAM,\n");
			bfw.write(" PARTITION p2 VALUES IN (3) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p3 VALUES IN (4) ENGINE = MyISAM,\n");
			bfw.write(" PARTITION p4 VALUES IN (5) ENGINE = MyISAM,\n");
			bfw.write(" PARTITION p5 VALUES IN (6) ENGINE = MyISAM,\n");
			bfw.write(" PARTITION p6 VALUES IN (7) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p7 VALUES IN (8) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p8 VALUES IN (9) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p9 VALUES IN (10) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p10 VALUES IN (11) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p11 VALUES IN (12) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p12 VALUES IN (13) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p13 VALUES IN (14) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p14 VALUES IN (15) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p15 VALUES IN (16) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p16 VALUES IN (17) ENGINE = MyISAM,\n");
            bfw.write("PARTITION p17 VALUES IN (18) ENGINE = MyISAM,\n");
            bfw.write("PARTITION p18 VALUES IN (19) ENGINE = MyISAM,\n");
            bfw.write("PARTITION p19 VALUES IN (20) ENGINE = MyISAM,\n");
            bfw.write("PARTITION p20 VALUES IN (21) ENGINE = MyISAM,\n");
            bfw.write("PARTITION p21 VALUES IN (22) ENGINE = MyISAM,\n");
            bfw.write("PARTITION p22 VALUES IN (23) ENGINE = MyISAM,\n");
		    bfw.write("PARTITION p23 VALUES IN (24) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p24 VALUES IN (25) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p25 VALUES IN (26) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p26 VALUES IN (27) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p27 VALUES IN (28) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p28 VALUES IN (29) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p29 VALUES IN (30) ENGINE = MyISAM,\n");
			bfw.write("PARTITION p30 VALUES IN (31) ENGINE = MyISAM) */;\n");


		}
		catch (FileNotFoundException e)
		{
			e.printStackTrace();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		finally
		{
			if (bfw != null)
			{
				try
				{
					bfw.close();
				}
				catch (IOException e)
				{
					e.printStackTrace();
				}
			}
		}

	}

	/**
	 * Gets script to generate a column
	 * 
	 * @param name attribute name
	 * @param dataType database data type
	 * @param key flag to indicate if current attribute is part of the primary
	 *            key
	 * @param nullable flag to indicate if current attribute is nullable
	 * @param defaultValue
	 * @return script to generate a column
	 */
	private String getColumn(String name, String dataType, String nullable,
			String defaultValue, String autoIncrement)
	{
		StringBuffer sb = new StringBuffer();
		sb.append("\t" + name + " " + dataType);
		if (!nullable.equalsIgnoreCase(Constant.YES))
		{
			sb.append(" NOT NULL");
		}
		if (autoIncrement.equalsIgnoreCase(Constant.YES))
		{
			sb.append(" AUTO_INCREMENT");
		}
		sb.append(",\n");
		return sb.toString();
	}

	/**
	 * Get the column name from the attribute name
	 * 
	 * @param attribute attribute name
	 * @return column name
	 */
	private String getColumnName(String attribute)
	{
		StringBuffer sb = new StringBuffer();
		char[] c = attribute.toCharArray();
		for (int i = 0; i < c.length; i++)
		{
			/*if (c[i] >= 'A' && c[i] <= 'Z')
			{
				sb.append(("_" + c[i]).toLowerCase());
			}
			else
			{*/
				sb.append(c[i]);
//			}
		}
		return sb.toString();
	}

	/**
	 * Gets the primary key constraint
	 * 
	 * @param tableName table name
	 * @param table table attributes
	 * @return the script for the table primary key constraint
	 */
	private String getPrimaryKeyConstraint(String tableName, String[][] table)
	{
		StringBuffer sb = new StringBuffer();
		sb.append("\tCONSTRAINT pk_" + tableName + " PRIMARY KEY (");
		boolean isFirst = true;
		for (int i = 0; i < table.length; i++)
		{
			if ((table[i][4]).equalsIgnoreCase(Constant.YES))
			{
				if (isFirst)
				{
					sb.append(getColumnName(table[i][Constant.C_NAME]));
				}
				else
				{
					sb.append(", " + getColumnName(table[i][Constant.C_NAME]));
				}
				isFirst = false;
			}
		}
		sb.append(")");
		return sb.toString();
	}

	/**
	 * Gets the unique constraint
	 * 
	 * @param tableName table name
	 * @param table table attributes
	 * @return the script for the table unique constraint
	 */
	private String getUniqueConstraint(String tableName, String[][] table)
	{
		StringBuffer sb = new StringBuffer();
		sb.append("\tCONSTRAINT uk_" + tableName + " UNIQUE (");
		boolean isFirst = true;
		boolean hasUnique = false;
		for (int i = 0; i < table.length; i++)
		{
			if ((table[i][Constant.C_UNIQUE_KEY]).equalsIgnoreCase(Constant.YES)&& !(table[i][Constant.C_NON_PERSISTENT]).equalsIgnoreCase(Constant.YES))
			{
				hasUnique = true;
				if (isFirst)
				{
					sb.append(getColumnName(table[i][Constant.C_NAME]));
				}
				else
				{
					sb.append(", " + getColumnName(table[i][Constant.C_NAME]));
				}
				isFirst = false;
			}
		}
		sb.append(")");
		// If no unique key, returns null
		if (!hasUnique)
		{
			return null;
		}
		return sb.toString();
	}

	private String getTableName(String fileName)
	{
		StringBuffer sb = new StringBuffer();
		sb.append(fileName.substring(0, 1).toLowerCase());
		for (int i = 1; i < fileName.length(); i++)
		{
			if (fileName.charAt(i) >= 'A' && fileName.charAt(i) <= 'Z')
			{
				sb.append("_" + fileName.substring(i, i + 1).toLowerCase());
			}
			else
			{
				sb.append(fileName.charAt(i));
			}
		}
		return sb.toString();
	}

}
